# coding=utf8
from openpyxl import load_workbook
import csv

import sys
reload(sys)
sys.setdefaultencoding('utf-8')

def save_data(fname, all_sql):
    file_object = open(fname, 'w')
    file_object.writelines(all_sql)
    file_object.close()


def gen_data(fname):
    insert_sql = ""
    print 'start.......'
    # 打开excel数据文件
    csvReader = csv.reader(open(fname, 'rb'))  # 以只读方式取得csv文件中内容
    rownum = 0
    for row in csvReader:  # 行循环
        if rownum > 500:
            break
        rownum = rownum + 1
        print 'Row No.:', rownum
        parameterStr = ','.join(row)  # 通过逗号连接每行每个单元格的内容
        a = parameterStr.replace('null', '')
        parameters = a.split(',')  # 得到每个单元格的内容
        datamonth = parameters[0]
        province = parameters[1]
        city = parameters[2]
        town = parameters[3]
        brand = parameters[4]
        vehicle_model = parameters[5]
        vehicle_series = parameters[6]
        vehicle_name = parameters[7]
        vehicle_body = parameters[8]
        color = parameters[9]
        displacement = parameters[10]
        domestic = parameters[11]
        country = parameters[12]
        manufacturer = parameters[13]
        birthyear = parameters[14]
        gender = parameters[15]
        usage = parameters[16]
        ownership = parameters[17]
        paytype = parameters[18]
        number = parameters[19]

        insert_sql = insert_sql + u"insert into cvm_gccyc_2015 (datamonth, province, city, " \
                     u"town, brand, vehicle_model, vehicle_series, vehicle_name, " \
                     u"vehicle_body, color, displacement, domestic, country, " \
                     u"manufacturer, birthyear, gender, usefor, ownership, paytype, number) " \
                     u"values('" + datamonth + u"', '" + province + u"', '" + city + u"', '" \
                     + town + u"', '" + brand + u"', '" + vehicle_model + u"', '" + vehicle_series + u"', '" \
                     + vehicle_name + u"', '" + vehicle_body + u"', '" + color + u"', '" + displacement + u"', '" \
                     + domestic + u"', '" + country + u"', '" + manufacturer + u"', '" + birthyear + u"', '" \
                     + gender + u"', '" + usage + u"', '" + ownership + u"', '" + paytype + u"', " + number + u");\n"
    # 保存sql到文件
    save_data('gccyc_201508_500.sql', insert_sql)
    print 'over.......'


if __name__ == '__main__':
    gen_data('gccyc_201508_500.csv')
